<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>F5填赛果</title>

</head>
<body>
<div class="custom-mount"></div>

<script>
    // “时间”、“目标球队”列的所有数据
    var dataArray = [];
    // 文档
    var jssdk;
    // 活动工作簿中的活动工作表
    var activeSheet;
    // 工作表上的所有列
    var allColumns;
    // 数据完全获取标志
    var flag = 'false';
    // 数据处理完全标志
    var dealFlag = 'false';

    // 文档加载
    async function fileLoad(fileUrl) {
        dataArray = [];
        jssdk = WebOfficeSDK.config({
            url: fileUrl,
            mount: document.querySelector('.custom -mount'),
        })
        await jssdk.ready();
        let app = jssdk.Application;
        // 活动工作簿中的活动工作表
        activeSheet = await app.ActiveWorkbook.ActiveSheet;
        // 工作表上的所有列
        allColumns = await activeSheet.Columns;
        // 获取“时间”列
        let timeColumn = allColumns.Item(2);
        // 获取“时间”列单元格
        let timeCells = timeColumn.Cells;

        // 获取“目标球队”列
        let targetColumn = allColumns.Item(7);
        // 获取“目标球队”列单元格
        let targetCells = targetColumn.Cells;

        // 当前行
        let rowNum = 3;

        while (true) {
            // 获取指定行的“时间”列单元格内容
            let timeText = await timeCells.Item(rowNum).Text;

            // 遇到第一个为空的，则退出
            if (!timeText) {
                flag = 'true';
                break;
            } else {
                // 获取指定行的“目标球队”列单元格超链接
                let targetItem = await targetCells.Item(rowNum++);
                let targetName = await targetItem.Text;
                let hyperlinks = await targetItem.Hyperlinks;
                let linkCount = await hyperlinks.Count;
                let linkText = "";
                if (linkCount != 0) {
                    linkText = await hyperlinks.Item(1).Address;
                }
                let item = [timeText, linkText, targetName];
                dataArray.push(item);
            }
        }
    }

    // 处理数据并回填
    async function f5FillIn(targetTimeAndSingJson) {
        console.log("开始执行 F5 填赛果");
        dealFlag = 'false';
        console.log(targetTimeAndSingJson);
        let targetTimeAndSingList = JSON.parse(targetTimeAndSingJson);
        console.log(targetTimeAndSingList);

        // 工作表上的所有行
        allRows = await activeSheet.Rows;
        // 获取“方向/拐点”列及单元格
        let direColumn = allColumns.Item(8);
        // 获取“方向/拐点”列单元格
        let direCells = direColumn.Cells;
        // 获取“连场”列及单元格
        let doubleColumn = allColumns.Item(9);
        // 获取“连场”列单元格
        let doubleCells = doubleColumn.Cells;
        // 获取“赔率”列及单元格
        let lossColumn = allColumns.Item(10);
        // 获取“赔率”列单元格
        let lossCells = lossColumn.Cells;

        // 当前行
        let rowNum = 3;
        for (let i = 0; i < targetTimeAndSingList.length; i++) {
            // 目标时间
            let time = targetTimeAndSingList[i][0];
            // 目标单双数
            let single = targetTimeAndSingList[i][1];

            // 获取表格当前行的时间，判定比赛是否正常进行
            console.log("第【" + (i + 1) + "】行，表格时间为：" + dataArray[i][0] + "，最近一场比赛时间为：" + time + "，比赛是否正常进行：" + isDateEqual(dataArray[i][0], time, false));
            // 0、设置背景色为淡紫色
            rowItem = await allRows.Item(rowNum);
            let rowColumns = await rowItem.Columns;
            for (let x = 1; x <= 10; x++) {
                let columnItem = await rowColumns.Item(x);
                let interior = columnItem.Interior;
                interior.Color = '#e4dfec';
            }

            if (isDateEqual(dataArray[i][0], time, false)) {
                // 1、获取预测单双数
                let doubleText = await doubleCells.Item(rowNum).Text;
                console.log("预测结果：" + doubleText + "，实际单双数：" + single);
                // 2、“连场”列仅保留连场数，字体颜色改为黑色
                let doubleItem = await doubleCells.Item(rowNum);
                // doubleItem.Value = doubleText.slice(0, 1);
                let doubleFont = await doubleItem.Font;
                doubleFont.Color = '#000000';
                if (!doubleText.includes(single)) {
                    // 3、预测正确：目标球队单双数 != “连场”列单双数
                    // 3-1、“方向/拐点”列填“√”
                    let direItem = await direCells.Item(rowNum);
                    direItem.Value = "√";
                    // 3-2、“赔率”列填空值
                    let lossItem = await lossCells.Item(rowNum);
                    lossItem.Value = "";
                    lossItem.NumberFormat = '0_;[红色]';
                } else {
                    // 4、预测失败：单双数匹配
                    // 4-1、“赔率”列填“-1”（整数、无小数点、红色）
                    let lossItem = await lossCells.Item(rowNum);
                    lossItem.Value = -1;
                    let lossFont = await lossItem.Font;
                    lossFont.Color = '#FF0000';
                    lossItem.NumberFormat = '0_;[红色]'
                    // 4-2、“方向/拐点”列填空值
                    let direItem = await direCells.Item(rowNum);
                    direItem.Value = "";
                }
            } else {
                // 5、比赛未进行：“赔率”列填“人工复验”
                let lossItem = await lossCells.Item(rowNum);
                lossItem.NumberFormat = '0_;[红色]';
                lossItem.Value = "人工复验";
            }
            rowNum++;
        }
        dealFlag = 'true';
    }

    // F5检查比赛回填数据
    async function f5CheckMatchFillIn(futureMatchListJson) {
        dealFlag = 'false';
        console.log("开始执行F5检查比赛");
        console.log(futureMatchListJson);
        let futureMatchList = JSON.parse(futureMatchListJson);
        console.log(futureMatchList);
        // 工作表上的所有行
        allRows = await activeSheet.Rows;
        // 获取“比赛时间”列及单元格
        let timeColumn = allColumns.Item(2);
        let timeCells = timeColumn.Cells;
        // 获取“主队”列及单元格
        let masterColumn = allColumns.Item(3);
        let masterCells = masterColumn.Cells;
        // 获取“客队”列及单元格
        let guestColumn = allColumns.Item(5);
        let guestCells = guestColumn.Cells;
        // 获取“连场”列及单元格
        let doubleColumn = allColumns.Item(9);
        let doubleCells = doubleColumn.Cells;
        // 获取“L”列及单元格
        let lColumn = allColumns.Item(12);
        // 获取“L”列单元格
        let lCells = lColumn.Cells;

        // 当前行
        let rowNum = 2;
        for (let i = 0; i < futureMatchList.length; i++) {
            rowNum++;

            // 比赛时间
            let time = futureMatchList[i][0];

            // 主队
            let masterTeam = futureMatchList[i][1];
            let masterLink = futureMatchList[i][2];
            // 客队
            let guestTeam = futureMatchList[i][3];
            let guestLink = futureMatchList[i][4];

            // 目标单双数
            let single = futureMatchList[i][5];
            let num = futureMatchList[i][6];
            let singleTranslate = "";
            if (single && num) {
                singleTranslate = num + (single == "true" ? "单" : "双");
            }

            // 检查对手球队、目标球队单双数
            let masterText = await masterCells.Item(rowNum).Text;
            let guestText = await guestCells.Item(rowNum).Text;
            let doubleText = await doubleCells.Item(rowNum).Text;
            if (masterText != masterTeam || guestText != guestTeam || singleTranslate != doubleText) {
                console.log("第【" + (i + 1) + "】行，对手球队或目标球队单双数不一致：");
                console.log(masterText + " " + masterTeam);
                console.log(guestText + " " + guestTeam);
                console.log(singleTranslate + " " + doubleText);
                // L 填“人工复验”
                let lItem = await lCells.Item(rowNum);
                lItem.NumberFormat = '0_;[红色]';
                lItem.Value = "人工复验";
                continue;
            }

            // 全行涂淡紫色，L 填紫色 release
            rowItem = await allRows.Item(rowNum);
            let rowColumns = await rowItem.Columns;
            for (let x = 1; x <= 10; x++) {
                let columnItem = await rowColumns.Item(x);
                let interior = columnItem.Interior;
                interior.Color = '#e4dfec';
            }
            let lItem = await lCells.Item(rowNum);
            let lFont = await lItem.Font;
            lFont.Color = '#7030a0';
            lItem.Value = "release";

            // 检查比赛时间
            let timeText = await timeCells.Item(rowNum).Text;
            if (!isDateEqual(timeText, time, true)) {
                console.log("第【" + (i + 1) + "】行，比赛时间不一致");
                console.log(timeText + " " + time);
                // 回填正确时间，底色改为黄色
                let timeItem = await timeCells.Item(rowNum);
                let interior = timeItem.Interior;
                interior.Color = '#ffff1a';
                timeItem.Value = time;
            }
        }
        dealFlag = 'true';
    }

    // 判断日期是否相等
    function isDateEqual(date1, date2, timeFlag) {
        let parseDate1 = new Date(date1);
        let parseDate2 = new Date(date2);

        // 比较时间
        if (timeFlag) {
            // 获取时分秒
            let hours1 = parseDate1.getHours();
            let minutes1 = parseDate1.getMinutes();
            let seconds1 = parseDate1.getSeconds();
            let hours2 = parseDate2.getHours();
            let minutes2 = parseDate2.getMinutes();
            let seconds2 = parseDate2.getSeconds();

            // 比较时分秒
            if (hours1 !== hours2 || minutes1 !== minutes2 || seconds1 !== seconds2) {
                return false;
            }
        }

        // 返回两个日期是否一致
        return parseDate1.getFullYear() === parseDate2.getFullYear() &&
            parseDate1.getMonth() === parseDate2.getMonth() &&
            parseDate1.getDate() === parseDate2.getDate();
    }
</script>
<script src="bin/web-office-sdk-v1.1.19.umd.js"></script>
</body>
<style>
    * {
        margin: 0;
        padding: 0;
    }

    html, body {
        width: 100%;
        height: 100%;
        overflow: hidden;
    }

    .custom-mount {
        width: 100%;
        height: 100%;
    }
</style>
</html>